{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "7d49ff2a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "2606f9ac",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "最优配送站为：良友大厦\n",
      "理由：\n",
      "  - 配送路线数较多（369条），显示出较高的配送能力。\n",
      "  - 平均最短距离最小（1787.66公里），有助于提高配送效率。\n",
      "  - 网点数量较多（195个），显示出较高的服务覆盖范围。\n",
      "\n",
      "建议方案：\n",
      "  - 建议在良友大厦建立配送中心，\n",
      "  - 基于较高的配送能力和效率，优化该配送中心的车辆调度和路线规划，\n",
      "  - 扩大服务范围，覆盖更多的快递网点。\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 加载Excel数据的函数\n",
    "def load_excel_data(file_path):\n",
    "    return pd.read_excel(file_path)\n",
    "\n",
    "# 计算评估指标的函数\n",
    "def calculate_metrics(df):\n",
    "    # 网点数量（即目的地的数量，体现密集度）\n",
    "    unique_destinations = df['目的地'].nunique()\n",
    "    \n",
    "    # 最短距离的平均值\n",
    "    avg_distance = df['最短距离'].mean()\n",
    "    \n",
    "    # 配送路线数的数量（即行车方案数的总和）\n",
    "    total_routes = df['行车方案数'].sum()\n",
    "    \n",
    "    # 返回评估指标的字典\n",
    "    return {\n",
    "        '网点数量': unique_destinations,\n",
    "        '平均最短距离': avg_distance,\n",
    "        '配送路线数': total_routes\n",
    "    }\n",
    "\n",
    "# 评估所有配送站并找出最优的\n",
    "def evaluate_distribution_centers(file_paths):\n",
    "    # 存储每个配送站的评估指标\n",
    "    metrics = {}\n",
    "    \n",
    "    # 遍历所有配送站的Excel文件\n",
    "    for file_path in file_paths:\n",
    "        # 加载数据\n",
    "        df = load_excel_data(file_path)\n",
    "        \n",
    "        # 提取配送站名称（这里假设文件名包含了配送站名称）\n",
    "        station_name = file_path.split('.')[0]\n",
    "        \n",
    "        # 计算评估指标\n",
    "        metrics[station_name] = calculate_metrics(df)\n",
    "    \n",
    "    # 根据评估指标进行排序，优先考虑配送路线数，然后是平均最短距离，最后是网点数量\n",
    "    # 注意：这里使用了lambda函数进行排序，假设配送路线数越多越好，平均最短距离越小越好，网点数量越多越好\n",
    "    sorted_stations = sorted(metrics.items(), key=lambda x: (x[1]['平均最短距离'],-x[1]['配送路线数'], x[1]['网点数量']))\n",
    "    \n",
    "    # 最优配送站为排序后的第一个\n",
    "    optimal_station, optimal_metrics = sorted_stations[0]\n",
    "    \n",
    "    return optimal_station, optimal_metrics\n",
    "\n",
    "# 主函数\n",
    "def main():\n",
    "    # 配送站的Excel文件路径\n",
    "    file_paths = [\n",
    "        '中诚大厦.xlsx',\n",
    "        '良友大厦.xlsx',\n",
    "        '武银大厦.xlsx'\n",
    "    ]\n",
    "    \n",
    "    # 评估所有配送站并找出最优的\n",
    "    optimal_station, optimal_metrics = evaluate_distribution_centers(file_paths)\n",
    "    \n",
    "    # 输出结果\n",
    "    print(f\"最优配送站为：{optimal_station}\")\n",
    "    print(f\"理由：\")\n",
    "    print(f\"  - 配送路线数较多（{optimal_metrics['配送路线数']}条），显示出较高的配送能力。\")\n",
    "    print(f\"  - 平均最短距离最小（{optimal_metrics['平均最短距离']:.2f}公里），有助于提高配送效率。\")\n",
    "    print(f\"  - 网点数量较多（{optimal_metrics['网点数量']}个），显示出较高的服务覆盖范围。\\n\")\n",
    "    \n",
    "    print(f\"建议方案：\")\n",
    "    print(f\"  - 建议在{optimal_station}建立配送中心，\")\n",
    "    print(f\"  - 基于较高的配送能力和效率，优化该配送中心的车辆调度和路线规划，\")\n",
    "    print(f\"  - 扩大服务范围，覆盖更多的快递网点。\")\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    main()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d2e2ffa4",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "193a9c7f",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d523f76c",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aeba3af6",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c5a258f6",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
